{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# AdventureWorks - Hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11.\n",
    "**For every customer with a 'Main Office' in Dallas show AddressLine1 of the 'Main Office' and AddressLine1 of the 'Shipping' address - if there is no shipping address leave it blank. Use one row per customer.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>CompanyName</th>\n",
       "        <th>main_office</th>\n",
       "        <th>shipping</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Elite Bikes</td>\n",
       "        <td>Po Box 8259024</td>\n",
       "        <td>9178 Jumping St.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rental Bikes</td>\n",
       "        <td>99828 Routh Street, Suite 825</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Third Bike Store</td>\n",
       "        <td>2500 North Stemmons Freeway</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Town Industries</td>\n",
       "        <td>P.O. Box 6256916</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Unsurpassed Bikes</td>\n",
       "        <td>Po Box 8035996</td>\n",
       "        <td></td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Elite Bikes', 'Po Box 8259024', '9178 Jumping St.'),\n",
       " ('Rental Bikes', '99828 Routh Street, Suite 825', ''),\n",
       " ('Third Bike Store', '2500 North Stemmons Freeway', ''),\n",
       " ('Town Industries', 'P.O. Box 6256916', ''),\n",
       " ('Unsurpassed Bikes', 'Po Box 8035996', '')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT \"CustomerAW\".\"CustomerID\" FROM\n",
    "      \"CustomerAW\" JOIN \"CustomerAddress\" ON (\n",
    "          \"CustomerAW\".\"CustomerID\"=\"CustomerAddress\".\"CustomerID\") JOIN\n",
    "        \"Address\" ON (\"CustomerAddress\".\"AddressID\"=\"Address\".\"AddressID\")\n",
    "        WHERE \"Address\".\"City\"='Dallas' AND\n",
    "            \"CustomerAddress\".\"AddressType\"='Main Office'\n",
    ")\n",
    "SELECT \"CustomerAW\".\"CompanyName\",\n",
    "  MAX(CASE WHEN \"AddressType\"='Main Office' THEN \"AddressLine1\" ELSE '' END) main_office,\n",
    "    MAX(CASE WHEN \"AddressType\"='Shipping' THEN \"AddressLine1\" ELSE '' END) shipping\n",
    "    FROM \"CustomerAW\" JOIN \"CustomerAddress\" ON (\n",
    "          \"CustomerAW\".\"CustomerID\"=\"CustomerAddress\".\"CustomerID\") JOIN\n",
    "        \"Address\" ON (\"CustomerAddress\".\"AddressID\"=\"Address\".\"AddressID\")\n",
    "        WHERE \"CustomerAW\".\"CustomerID\" IN (SELECT \"CustomerID\" FROM t)\n",
    "        GROUP BY \"CustomerAW\".\"CompanyName\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12.\n",
    "**For each order show the SalesOrderID and SubTotal calculated three ways:**\n",
    "\n",
    "- **A) From the SalesOrderHeader**\n",
    "- **B) Sum of OrderQty*UnitPrice**\n",
    "- **C) Sum of OrderQty*ListPrice**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "31 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>SalesOrderID</th>\n",
       "        <th>subtotal_a</th>\n",
       "        <th>subtotal_b</th>\n",
       "        <th>subtotal_c</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71774</td>\n",
       "        <td>880.35</td>\n",
       "        <td>713.8000</td>\n",
       "        <td>1189.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71776</td>\n",
       "        <td>78.81</td>\n",
       "        <td>63.9000</td>\n",
       "        <td>106.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71780</td>\n",
       "        <td>38418.69</td>\n",
       "        <td>29922.8100</td>\n",
       "        <td>56651.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71782</td>\n",
       "        <td>39785.33</td>\n",
       "        <td>33319.6800</td>\n",
       "        <td>55533.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71783</td>\n",
       "        <td>83858.43</td>\n",
       "        <td>65682.7396</td>\n",
       "        <td>121625.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71784</td>\n",
       "        <td>108561.83</td>\n",
       "        <td>89868.8795</td>\n",
       "        <td>151932.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71796</td>\n",
       "        <td>57634.63</td>\n",
       "        <td>47848.0200</td>\n",
       "        <td>79746.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71797</td>\n",
       "        <td>78029.69</td>\n",
       "        <td>65122.7911</td>\n",
       "        <td>108986.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71815</td>\n",
       "        <td>1141.58</td>\n",
       "        <td>926.9100</td>\n",
       "        <td>1544.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71816</td>\n",
       "        <td>3398.17</td>\n",
       "        <td>2847.3700</td>\n",
       "        <td>4745.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71831</td>\n",
       "        <td>2016.34</td>\n",
       "        <td>1712.9100</td>\n",
       "        <td>2854.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71832</td>\n",
       "        <td>35775.21</td>\n",
       "        <td>28950.4766</td>\n",
       "        <td>50559.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71845</td>\n",
       "        <td>41622.05</td>\n",
       "        <td>34118.3000</td>\n",
       "        <td>57768.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71846</td>\n",
       "        <td>2453.76</td>\n",
       "        <td>1884.3800</td>\n",
       "        <td>3592.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71856</td>\n",
       "        <td>602.19</td>\n",
       "        <td>500.3000</td>\n",
       "        <td>833.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71858</td>\n",
       "        <td>13823.71</td>\n",
       "        <td>11528.8000</td>\n",
       "        <td>19214.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71863</td>\n",
       "        <td>3324.28</td>\n",
       "        <td>2777.0538</td>\n",
       "        <td>4633.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71867</td>\n",
       "        <td>1059.31</td>\n",
       "        <td>858.9000</td>\n",
       "        <td>1431.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71885</td>\n",
       "        <td>550.39</td>\n",
       "        <td>524.6400</td>\n",
       "        <td>874.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>71895</td>\n",
       "        <td>246.74</td>\n",
       "        <td>221.2400</td>\n",
       "        <td>368.76</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(71774, Decimal('880.35'), Decimal('713.8000'), Decimal('1189.66')),\n",
       " (71776, Decimal('78.81'), Decimal('63.9000'), Decimal('106.50')),\n",
       " (71780, Decimal('38418.69'), Decimal('29922.8100'), Decimal('56651.56')),\n",
       " (71782, Decimal('39785.33'), Decimal('33319.6800'), Decimal('55533.31')),\n",
       " (71783, Decimal('83858.43'), Decimal('65682.7396'), Decimal('121625.43')),\n",
       " (71784, Decimal('108561.83'), Decimal('89868.8795'), Decimal('151932.58')),\n",
       " (71796, Decimal('57634.63'), Decimal('47848.0200'), Decimal('79746.71')),\n",
       " (71797, Decimal('78029.69'), Decimal('65122.7911'), Decimal('108986.40')),\n",
       " (71815, Decimal('1141.58'), Decimal('926.9100'), Decimal('1544.86')),\n",
       " (71816, Decimal('3398.17'), Decimal('2847.3700'), Decimal('4745.68')),\n",
       " (71831, Decimal('2016.34'), Decimal('1712.9100'), Decimal('2854.91')),\n",
       " (71832, Decimal('35775.21'), Decimal('28950.4766'), Decimal('50559.01')),\n",
       " (71845, Decimal('41622.05'), Decimal('34118.3000'), Decimal('57768.21')),\n",
       " (71846, Decimal('2453.76'), Decimal('1884.3800'), Decimal('3592.65')),\n",
       " (71856, Decimal('602.19'), Decimal('500.3000'), Decimal('833.84')),\n",
       " (71858, Decimal('13823.71'), Decimal('11528.8000'), Decimal('19214.74')),\n",
       " (71863, Decimal('3324.28'), Decimal('2777.0538'), Decimal('4633.78')),\n",
       " (71867, Decimal('1059.31'), Decimal('858.9000'), Decimal('1431.50')),\n",
       " (71885, Decimal('550.39'), Decimal('524.6400'), Decimal('874.44')),\n",
       " (71895, Decimal('246.74'), Decimal('221.2400'), Decimal('368.76')),\n",
       " (71897, Decimal('12685.89'), Decimal('10585.0100'), Decimal('17641.75')),\n",
       " (71898, Decimal('63980.99'), Decimal('53248.5700'), Decimal('88747.82')),\n",
       " (71899, Decimal('2415.67'), Decimal('1856.1800'), Decimal('3545.67')),\n",
       " (71902, Decimal('74058.81'), Decimal('59893.7200'), Decimal('106151.57')),\n",
       " (71915, Decimal('2137.23'), Decimal('1732.8600'), Decimal('2888.15')),\n",
       " (71917, Decimal('40.90'), Decimal('37.7300'), Decimal('62.93')),\n",
       " (71920, Decimal('2980.79'), Decimal('2527.0800'), Decimal('4211.88')),\n",
       " (71923, Decimal('106.54'), Decimal('96.0312'), Decimal('166.81')),\n",
       " (71935, Decimal('6634.30'), Decimal('5533.7754'), Decimal('9229.27')),\n",
       " (71936, Decimal('98278.69'), Decimal('79589.0848'), Decimal('138124.87')),\n",
       " (71938, Decimal('88812.86'), Decimal('5102.9500'), Decimal('8504.95'))]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH a AS (\n",
    "    SELECT \"SalesOrderID\", \"SubTotal\" subtotal_a FROM \"SalesOrderHeader\"\n",
    "), b AS (\n",
    "    SELECT \"SalesOrderID\", SUM(\"OrderQty\"*\"UnitPrice\"*(1-\"UnitPriceDiscount\")) subtotal_b\n",
    "    FROM \"SalesOrderDetail\"\n",
    "    GROUP BY \"SalesOrderID\"\n",
    "), c AS (\n",
    "    SELECT \"SalesOrderID\", SUM(\"OrderQty\"*\"ListPrice\") subtotal_c\n",
    "    FROM \"SalesOrderDetail\" JOIN \"Product\" ON (\n",
    "        \"SalesOrderDetail\".\"ProductID\"=\"Product\".\"ProductID\")\n",
    "    GROUP BY \"SalesOrderID\"\n",
    ")\n",
    "SELECT a.\"SalesOrderID\", subtotal_a, subtotal_b, subtotal_c\n",
    "    FROM a JOIN b ON (a.\"SalesOrderID\"=b.\"SalesOrderID\") JOIN\n",
    "    c ON (a.\"SalesOrderID\"=c.\"SalesOrderID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13.\n",
    "**Show the best selling item by value.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "137 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>Name</th>\n",
       "        <th>subtotal</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 60</td>\n",
       "        <td>37191.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 42</td>\n",
       "        <td>37178.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 38</td>\n",
       "        <td>35801.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 48</td>\n",
       "        <td>33509.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Yellow, 60</td>\n",
       "        <td>23745.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 50</td>\n",
       "        <td>22887.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 42</td>\n",
       "        <td>20879.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 40</td>\n",
       "        <td>20411.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Black, 46</td>\n",
       "        <td>19277.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 46</td>\n",
       "        <td>18095.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Yellow, 46</td>\n",
       "        <td>17165.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-1000 Blue, 46</td>\n",
       "        <td>17165.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-350-W Yellow, 42</td>\n",
       "        <td>15887.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mountain-200 Silver, 38</td>\n",
       "        <td>15311.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-250 Black, 44</td>\n",
       "        <td>13194.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-2000 Blue, 54</td>\n",
       "        <td>13120.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HL Mountain Frame - Silver, 38</td>\n",
       "        <td>13099.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Touring-3000 Blue, 50</td>\n",
       "        <td>10244.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>HL Mountain Frame - Black, 42</td>\n",
       "        <td>9717.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Road-550-W Yellow, 38</td>\n",
       "        <td>9412.06</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">137 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Touring-1000 Blue, 60', Decimal('37191.44')),\n",
       " ('Mountain-200 Black, 42', Decimal('37178.73')),\n",
       " ('Mountain-200 Black, 38', Decimal('35801.74')),\n",
       " ('Road-350-W Yellow, 48', Decimal('33509.58')),\n",
       " ('Touring-1000 Yellow, 60', Decimal('23745.32')),\n",
       " ('Touring-1000 Blue, 50', Decimal('22887.04')),\n",
       " ('Mountain-200 Silver, 42', Decimal('20879.85')),\n",
       " ('Road-350-W Yellow, 40', Decimal('20411.80')),\n",
       " ('Mountain-200 Black, 46', Decimal('19277.86')),\n",
       " ('Mountain-200 Silver, 46', Decimal('18095.87')),\n",
       " ('Touring-1000 Yellow, 46', Decimal('17165.28')),\n",
       " ('Touring-1000 Blue, 46', Decimal('17165.28')),\n",
       " ('Road-350-W Yellow, 42', Decimal('15887.18')),\n",
       " ('Mountain-200 Silver, 38', Decimal('15311.89')),\n",
       " ('Road-250 Black, 44', Decimal('13194.09')),\n",
       " ('Touring-2000 Blue, 54', Decimal('13120.38')),\n",
       " ('HL Mountain Frame - Silver, 38', Decimal('13099.20')),\n",
       " ('Touring-3000 Blue, 50', Decimal('10244.43')),\n",
       " ('HL Mountain Frame - Black, 42', Decimal('9717.12')),\n",
       " ('Road-550-W Yellow, 38', Decimal('9412.06')),\n",
       " ('Touring-3000 Yellow, 62', Decimal('8744.85')),\n",
       " ('Touring-1000 Blue, 54', Decimal('8582.64')),\n",
       " ('HL Mountain Frame - Silver, 46', Decimal('8187.00')),\n",
       " ('Touring-3000 Yellow, 44', Decimal('8017.38')),\n",
       " ('Road-250 Black, 48', Decimal('7330.05')),\n",
       " ('Touring-2000 Blue, 60', Decimal('7289.10')),\n",
       " ('HL Touring Frame - Yellow, 60', Decimal('7228.20')),\n",
       " ('Touring-1000 Yellow, 50', Decimal('7152.20')),\n",
       " ('Touring-3000 Blue, 54', Decimal('6681.15')),\n",
       " ('Touring-2000 Blue, 50', Decimal('6560.19')),\n",
       " ('Touring-2000 Blue, 46', Decimal('6560.19')),\n",
       " ('Touring-1000 Yellow, 54', Decimal('5721.76')),\n",
       " ('HL Mountain Frame - Black, 38', Decimal('5668.32')),\n",
       " ('Road-750 Black, 48', Decimal('5183.84')),\n",
       " ('HL Road Frame - Black, 44', Decimal('5153.40')),\n",
       " ('HL Mountain Frame - Silver, 42', Decimal('4912.20')),\n",
       " ('ML Road Frame-W - Yellow, 48', Decimal('4282.80')),\n",
       " ('HL Touring Frame - Blue, 54', Decimal('4216.45')),\n",
       " ('Mountain-400-W Silver, 46', Decimal('4155.21')),\n",
       " ('Mountain-400-W Silver, 40', Decimal('4155.21')),\n",
       " ('Touring-3000 Yellow, 50', Decimal('4008.69')),\n",
       " ('Mountain-500 Black, 48', Decimal('3887.88')),\n",
       " ('Road-750 Black, 52', Decimal('3887.88')),\n",
       " ('ML Road Frame-W - Yellow, 44', Decimal('3569.00')),\n",
       " ('Road-750 Black, 58', Decimal('3563.89')),\n",
       " ('HL Touring Frame - Yellow, 54', Decimal('3011.75')),\n",
       " ('Classic Vest, S', Decimal('2968.93')),\n",
       " ('Road-250 Black, 52', Decimal('2932.02')),\n",
       " ('HL Crankset', Decimal('2915.88')),\n",
       " ('Touring-3000 Blue, 58', Decimal('2672.46')),\n",
       " ('Touring-3000 Yellow, 54', Decimal('2672.46')),\n",
       " ('ML Mountain Frame-W - Silver, 40', Decimal('2621.40')),\n",
       " ('HL Road Frame - Red, 44', Decimal('2576.70')),\n",
       " ('HL Road Frame - Red, 62', Decimal('2576.70')),\n",
       " ('ML Mountain Frame-W - Silver, 42', Decimal('2402.95')),\n",
       " ('Mountain-400-W Silver, 42', Decimal('2308.45')),\n",
       " ('Touring-3000 Blue, 44', Decimal('2227.05')),\n",
       " ('Hitch Rack - 4-Bike', Decimal('2088.00')),\n",
       " ('Road-550-W Yellow, 40', Decimal('2016.87')),\n",
       " ('Road-550-W Yellow, 48', Decimal('2016.87')),\n",
       " (\"Women's Mountain Shorts, S\", Decimal('1977.72')),\n",
       " ('Mountain-500 Silver, 52', Decimal('1921.00')),\n",
       " ('Mountain-400-W Silver, 38', Decimal('1846.76')),\n",
       " ('LL Road Frame - Black, 58', Decimal('1820.97')),\n",
       " ('HL Touring Frame - Blue, 60', Decimal('1807.05')),\n",
       " ('LL Mountain Frame - Black, 44', Decimal('1798.44')),\n",
       " ('Short-Sleeve Classic Jersey, XL', Decimal('1643.78')),\n",
       " ('Mountain-500 Black, 44', Decimal('1619.95')),\n",
       " ('LL Road Frame - Black, 52', Decimal('1618.64')),\n",
       " ('Short-Sleeve Classic Jersey, L', Decimal('1499.64')),\n",
       " ('ML Road Frame-W - Yellow, 38', Decimal('1427.60')),\n",
       " ('LL Mountain Frame - Silver, 40', Decimal('1425.87')),\n",
       " ('LL Mountain Frame - Silver, 44', Decimal('1425.87')),\n",
       " ('Hydration Pack - 70 oz.', Decimal('1418.57')),\n",
       " ('Long-Sleeve Logo Jersey, L', Decimal('1397.02')),\n",
       " ('Mountain-500 Silver, 42', Decimal('1356.00')),\n",
       " ('LL Mountain Frame - Black, 42', Decimal('1348.83')),\n",
       " ('Touring-3000 Blue, 62', Decimal('1336.23')),\n",
       " ('Touring-3000 Yellow, 58', Decimal('1336.23')),\n",
       " ('LL Mountain Frame - Silver, 42', Decimal('1267.44')),\n",
       " ('Mountain-500 Silver, 40', Decimal('1243.00')),\n",
       " (\"Women's Mountain Shorts, L\", Decimal('1133.73')),\n",
       " ('Classic Vest, M', Decimal('1104.90')),\n",
       " ('HL Bottom Bracket', Decimal('1093.35')),\n",
       " ('LL Crankset', Decimal('1052.90')),\n",
       " ('Mountain-500 Black, 40', Decimal('971.97')),\n",
       " ('Mountain-500 Black, 52', Decimal('971.97')),\n",
       " ('Mountain-500 Black, 42', Decimal('971.97')),\n",
       " ('Short-Sleeve Classic Jersey, S', Decimal('971.70')),\n",
       " ('Sport-100 Helmet, Black', Decimal('941.04')),\n",
       " ('Front Brakes', Decimal('766.80')),\n",
       " ('Long-Sleeve Logo Jersey, M', Decimal('719.76')),\n",
       " ('Front Derailleur', Decimal('713.57')),\n",
       " ('Sport-100 Helmet, Blue', Decimal('708.40')),\n",
       " ('Mountain-500 Silver, 48', Decimal('678.00')),\n",
       " ('Road-550-W Yellow, 42', Decimal('672.29')),\n",
       " ('Sport-100 Helmet, Red', Decimal('671.68')),\n",
       " ('HL Touring Frame - Blue, 50', Decimal('602.35')),\n",
       " ('LL Touring Frame - Yellow, 44', Decimal('600.15')),\n",
       " ('LL Touring Frame - Blue, 54', Decimal('600.15')),\n",
       " ('LL Mountain Frame - Black, 48', Decimal('599.48')),\n",
       " ('HL Mountain Pedal', Decimal('583.08')),\n",
       " ('Rear Derailleur', Decimal('583.04')),\n",
       " ('ML Road Pedal', Decimal('484.25')),\n",
       " ('LL Mountain Frame - Silver, 52', Decimal('475.29')),\n",
       " ('Mountain-500 Silver, 44', Decimal('452.00')),\n",
       " ('ML Mountain Handlebars', Decimal('445.80')),\n",
       " ('HL Road Pedal', Decimal('437.31')),\n",
       " ('Long-Sleeve Logo Jersey, XL', Decimal('419.86')),\n",
       " ('LL Touring Frame - Yellow, 50', Decimal('400.10')),\n",
       " ('LL Touring Frame - Blue, 50', Decimal('400.10')),\n",
       " ('LL Road Pedal', Decimal('388.64')),\n",
       " ('ML Mountain Seat/Saddle', Decimal('375.68')),\n",
       " ('Half-Finger Gloves, M', Decimal('352.56')),\n",
       " ('LL Mountain Pedal', Decimal('291.48')),\n",
       " ('Half-Finger Gloves, L', Decimal('279.11')),\n",
       " ('AWC Logo Cap', Decimal('272.91')),\n",
       " ('Racing Socks, L', Decimal('257.27')),\n",
       " ('Bike Wash - Dissolver', Decimal('226.93')),\n",
       " ('LL Bottom Bracket', Decimal('226.73')),\n",
       " ('ML Mountain Pedal', Decimal('223.50')),\n",
       " ('HL Touring Seat/Saddle', Decimal('221.06')),\n",
       " ('HL Mountain Seat/Saddle', Decimal('221.06')),\n",
       " ('HL Road Handlebars', Decimal('216.48')),\n",
       " ('HL Mountain Handlebars', Decimal('216.48')),\n",
       " (\"Women's Mountain Shorts, M\", Decimal('209.95')),\n",
       " ('LL Touring Frame - Yellow, 62', Decimal('200.05')),\n",
       " ('LL Mountain Handlebars', Decimal('187.04')),\n",
       " ('Half-Finger Gloves, S', Decimal('176.28')),\n",
       " ('Water Bottle - 30 oz.', Decimal('135.34')),\n",
       " ('LL Mountain Seat/Saddle', Decimal('97.62')),\n",
       " ('Chain', Decimal('97.12')),\n",
       " ('Rear Brakes', Decimal('63.90')),\n",
       " ('HL Road Seat/Saddle', Decimal('63.16')),\n",
       " ('Racing Socks, M', Decimal('59.29')),\n",
       " ('HL Touring Handlebars', Decimal('54.94')),\n",
       " ('Patch Kit/8 Patches', Decimal('27.40'))]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \"Product\".\"Name\", SUM(\"OrderQty\"*\"UnitPrice\") subtotal\n",
    "    FROM \"SalesOrderDetail\" JOIN \"Product\" ON (\n",
    "        \"SalesOrderDetail\".\"ProductID\"=\"Product\".\"ProductID\")\n",
    "    GROUP BY \"Product\".\"ProductID\"\n",
    "    ORDER BY SUM(\"OrderQty\"*\"UnitPrice\") DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "**Show how many orders are in the following ranges (in $):**\n",
    "\n",
    "```\n",
    "    RANGE      Num Orders      Total Value\n",
    "    0-  99\n",
    "  100- 999\n",
    " 1000-9999\n",
    "10000-\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "4 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>RANGE</th>\n",
       "        <th>Num Orders</th>\n",
       "        <th>Total Value</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;&nbsp;&nbsp;0-  99</td>\n",
       "        <td>3</td>\n",
       "        <td>158.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>&nbsp;&nbsp;100- 999</td>\n",
       "        <td>5</td>\n",
       "        <td>2386.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td> 1000-9999</td>\n",
       "        <td>10</td>\n",
       "        <td>27561.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10000-    </td>\n",
       "        <td>14</td>\n",
       "        <td>835326.81</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('    0-  99', 3, Decimal('158.66')),\n",
       " ('  100- 999', 5, Decimal('2386.21')),\n",
       " (' 1000-9999', 10, Decimal('27561.43')),\n",
       " ('10000-    ', 14, Decimal('835326.81'))]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    -- generate the RANGE column\n",
    "    SELECT \"SubTotal\", \"SalesOrderID\",\n",
    "      CASE WHEN \"SubTotal\" BETWEEN 0 AND 99.99      THEN '    0-  99'\n",
    "           WHEN \"SubTotal\" BETWEEN 100 AND 999.99   THEN '  100- 999'\n",
    "           WHEN \"SubTotal\" BETWEEN 1000 AND 9999.99 THEN ' 1000-9999'\n",
    "           ELSE                                          '10000-    '\n",
    "      END \"RANGE\"\n",
    "    FROM \"SalesOrderHeader\"\n",
    ")\n",
    "SELECT \"RANGE\", COUNT(*) \"Num Orders\", SUM(\"SubTotal\") \"Total Value\" FROM t\n",
    "    GROUP BY \"RANGE\"\n",
    "    ORDER BY \"RANGE\";"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "**Identify the three most important cities. Show the break down of top level product category against city.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "42 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>City</th>\n",
       "        <th>Category</th>\n",
       "        <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Bottom Brackets</td>\n",
       "        <td>388.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Brakes</td>\n",
       "        <td>255.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Chains</td>\n",
       "        <td>36.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Cranksets</td>\n",
       "        <td>1773.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Derailleurs</td>\n",
       "        <td>638.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Gloves</td>\n",
       "        <td>88.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Handlebars</td>\n",
       "        <td>292.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Helmets</td>\n",
       "        <td>20.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Mountain Bikes</td>\n",
       "        <td>50881.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Mountain Frames</td>\n",
       "        <td>24018.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Pedals</td>\n",
       "        <td>390.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Road Bikes</td>\n",
       "        <td>5102.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Saddles</td>\n",
       "        <td>284.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>London</td>\n",
       "        <td>Shorts</td>\n",
       "        <td>1072.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Bike Racks</td>\n",
       "        <td>144.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Bottles and Cages</td>\n",
       "        <td>29.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Caps</td>\n",
       "        <td>57.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Cleaners</td>\n",
       "        <td>38.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Gloves</td>\n",
       "        <td>264.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Union City</td>\n",
       "        <td>Handlebars</td>\n",
       "        <td>72.16</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">42 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('London', 'Bottom Brackets', Decimal('388.73')),\n",
       " ('London', 'Brakes', Decimal('255.60')),\n",
       " ('London', 'Chains', Decimal('36.42')),\n",
       " ('London', 'Cranksets', Decimal('1773.81')),\n",
       " ('London', 'Derailleurs', Decimal('638.85')),\n",
       " ('London', 'Gloves', Decimal('88.14')),\n",
       " ('London', 'Handlebars', Decimal('292.63')),\n",
       " ('London', 'Helmets', Decimal('20.99')),\n",
       " ('London', 'Mountain Bikes', Decimal('50881.99')),\n",
       " ('London', 'Mountain Frames', Decimal('24018.80')),\n",
       " ('London', 'Pedals', Decimal('390.32')),\n",
       " ('London', 'Road Bikes', Decimal('5102.95')),\n",
       " ('London', 'Saddles', Decimal('284.43')),\n",
       " ('London', 'Shorts', Decimal('1072.14')),\n",
       " ('Union City', 'Bike Racks', Decimal('144.00')),\n",
       " ('Union City', 'Bottles and Cages', Decimal('29.90')),\n",
       " ('Union City', 'Caps', Decimal('57.31')),\n",
       " ('Union City', 'Cleaners', Decimal('38.16')),\n",
       " ('Union City', 'Gloves', Decimal('264.42')),\n",
       " ('Union City', 'Handlebars', Decimal('72.16')),\n",
       " ('Union City', 'Helmets', Decimal('721.69')),\n",
       " ('Union City', 'Hydration Packs', Decimal('296.91')),\n",
       " ('Union City', 'Jerseys', Decimal('1597.49')),\n",
       " ('Union City', 'Pedals', Decimal('490.73')),\n",
       " ('Union City', 'Road Bikes', Decimal('53478.76')),\n",
       " ('Union City', 'Road Frames', Decimal('10031.90')),\n",
       " ('Union City', 'Socks', Decimal('5.39')),\n",
       " ('Union City', 'Tires and Tubes', Decimal('8.22')),\n",
       " ('Union City', 'Vests', Decimal('904.95')),\n",
       " ('Woolston', 'Bike Racks', Decimal('432.00')),\n",
       " ('Woolston', 'Bottles and Cages', Decimal('31.79')),\n",
       " ('Woolston', 'Caps', Decimal('53.90')),\n",
       " ('Woolston', 'Cleaners', Decimal('38.16')),\n",
       " ('Woolston', 'Gloves', Decimal('161.59')),\n",
       " ('Woolston', 'Helmets', Decimal('495.36')),\n",
       " ('Woolston', 'Hydration Packs', Decimal('296.91')),\n",
       " ('Woolston', 'Jerseys', Decimal('1178.02')),\n",
       " ('Woolston', 'Saddles', Decimal('63.16')),\n",
       " ('Woolston', 'Tires and Tubes', Decimal('10.96')),\n",
       " ('Woolston', 'Touring Bikes', Decimal('77040.15')),\n",
       " ('Woolston', 'Touring Frames', Decimal('9430.95')),\n",
       " ('Woolston', 'Vests', Decimal('1108.19'))]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    -- the top three cities\n",
    "    SELECT \"Address\".\"City\" FROM\n",
    "      \"Address\" JOIN \"SalesOrderHeader\" ON (\n",
    "          \"SalesOrderHeader\".\"ShipToAddressID\"=\"Address\".\"AddressID\")\n",
    "    GROUP BY \"Address\".\"City\"\n",
    "    ORDER BY SUM(\"SubTotal\") DESC\n",
    "    LIMIT 3\n",
    ")\n",
    "SELECT \"Address\".\"City\", \"ProductCategory\".Name \"Category\",\n",
    "    SUM(\"OrderQty\"*\"UnitPrice\") amount FROM\n",
    "      \"Address\" JOIN \"SalesOrderHeader\" ON (\n",
    "        \"SalesOrderHeader\".\"ShipToAddressID\"=\"Address\".\"AddressID\") JOIN\n",
    "      \"SalesOrderDetail\" ON (\n",
    "        \"SalesOrderDetail\".\"SalesOrderID\"=\"SalesOrderHeader\".\"SalesOrderID\") JOIN\n",
    "      \"Product\" ON (\"Product\".\"ProductID\"=\"SalesOrderDetail\".\"ProductID\") JOIN\n",
    "      \"ProductCategory\" ON (\"Product\".\"ProductCategoryID\"=\"ProductCategory\".\"ProductCategoryID\")\n",
    "    WHERE \"Address\".\"City\" IN (SELECT \"City\" FROM t)\n",
    "    GROUP BY \"Address\".\"City\", \"ProductCategory\".name\n",
    "    ORDER BY \"Address\".\"City\", \"ProductCategory\".name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
